import { BaseModel } from './BaseModel.js';

export class ProductModel extends BaseModel {
    constructor() {
        super();
        this.tableName = 'products';
    }

    // 查找所有产品（支持筛选和分页）
    async findAll({ category, minPrice, maxPrice, search, limit, offset }) {
        try {
            const whereConditions = ["status != 'deleted'"];
            const params = [];
            
            if (category) {
                whereConditions.push('category = ?');
                params.push(category);
            }
            
            if (minPrice !== undefined) {
                whereConditions.push('price >= ?');
                params.push(minPrice);
            }
            
            if (maxPrice !== undefined) {
                whereConditions.push('price <= ?');
                params.push(maxPrice);
            }
            
            if (search) {
                whereConditions.push('(name LIKE ? OR description LIKE ? OR sku LIKE ?)');
                const searchTerm = `%${search}%`;
                params.push(searchTerm, searchTerm, searchTerm);
            }
            
            const whereClause = whereConditions.join(' AND ');
            
            return await super.findAll(this.tableName, {
                select: 'id, name, description, price, sku, category, stock, status, image_url, view_count, created_at, updated_at',
                where: whereClause,
                whereParams: [...params, limit, offset],
                orderBy: 'created_at DESC',
                limit,
                offset
            });
        } catch (error) {
            throw new Error(`Database query failed: ${error.message}`);
        }
    }

    // 统计产品数量
    async count({ category, minPrice, maxPrice, search }) {
        try {
            let query = `SELECT COUNT(*) as total FROM products WHERE status != 'deleted'`;
            const params = [];
            
            if (category) {
                query += ` AND category = ?`;
                params.push(category);
            }
            
            if (minPrice !== undefined) {
                query += ` AND price >= ?`;
                params.push(minPrice);
            }
            
            if (maxPrice !== undefined) {
                query += ` AND price <= ?`;
                params.push(maxPrice);
            }
            
            if (search) {
                query += ` AND (name LIKE ? OR description LIKE ? OR sku LIKE ?)`;
                const searchTerm = `%${search}%`;
                params.push(searchTerm, searchTerm, searchTerm);
            }
            
            const [rows] = await this.pool.execute(query, params);
            return rows[0].total;
        } catch (error) {
            throw new Error(`Database count failed: ${error.message}`);
        }
    }

    // 根据ID查找产品
    async findById(id) {
        try {
            return await super.findById(this.tableName, id, {
                select: 'id, name, description, price, sku, category, stock, status, image_url, view_count, created_at, updated_at',
                where: "status != 'deleted'"
            });
        } catch (error) {
            throw new Error(`Database query failed: ${error.message}`);
        }
    }

    // 根据名称查找产品
    async findByName(name) {
        try {
            const query = `
                SELECT id, name, sku 
                FROM products 
                WHERE name = ? AND status != 'deleted'
            `;
            
            const [rows] = await this.pool.execute(query, [name]);
            return rows[0] || null;
        } catch (error) {
            throw new Error(`Database query failed: ${error.message}`);
        }
    }

    // 根据名称查找产品（排除指定ID）
    async findByNameExcludeId(name, excludeId) {
        try {
            const query = `
                SELECT id, name, sku 
                FROM products 
                WHERE name = ? AND id != ? AND status != 'deleted'
            `;
            
            const [rows] = await this.pool.execute(query, [name, excludeId]);
            return rows[0] || null;
        } catch (error) {
            throw new Error(`Database query failed: ${error.message}`);
        }
    }

    // 创建新产品
    async create(productData) {
        try {
            const data = {
                name: productData.name,
                description: productData.description || null,
                price: productData.price,
                sku: productData.sku,
                category: productData.category || null,
                stock: productData.stock || 0,
                status: productData.status || 'active',
                image_url: productData.image_url || null,
                view_count: productData.view_count || 0,
                created_at: productData.created_at,
                updated_at: productData.updated_at
            };
            
            const result = await super.create(this.tableName, data);
            
            // 返回创建的产品
            return await this.findById(result.insertId);
        } catch (error) {
            throw new Error(`Database insert failed: ${error.message}`);
        }
    }

    // 更新产品
    async update(id, productData) {
        try {
            const allowedFields = ['name', 'description', 'price', 'category', 'stock', 'status', 'image_url'];
            const cleanData = {};
            
            for (const [key, value] of Object.entries(productData)) {
                if (allowedFields.includes(key) && value !== undefined) {
                    cleanData[key] = value;
                }
            }
            
            if (Object.keys(cleanData).length === 0) {
                throw new Error('No valid fields to update');
            }
            
            const result = await super.update(this.tableName, id, cleanData);
            
            if (result.affectedRows === 0) {
                throw new Error('Product not found or no changes made');
            }
            
            return await this.findById(id);
        } catch (error) {
            throw new Error(`Database update failed: ${error.message}`);
        }
    }

    // 软删除产品
    async softDelete(id) {
        try {
            const result = await super.softDelete(this.tableName, id, 'status', 'deleted');
            
            return {
                success: result.affectedRows > 0,
                affectedRows: result.affectedRows,
                message: result.affectedRows > 0 ? 'Product soft deleted successfully' : 'Product not found'
            };
        } catch (error) {
            throw new Error(`Database soft delete failed: ${error.message}`);
        }
    }

    // 硬删除产品
    async delete(id) {
        try {
            const result = await super.delete(this.tableName, id);
            
            return {
                success: result.affectedRows > 0,
                affectedRows: result.affectedRows,
                message: result.affectedRows > 0 ? 'Product deleted successfully' : 'Product not found'
            };
        } catch (error) {
            throw new Error(`Database delete failed: ${error.message}`);
        }
    }

    // 更新库存
    async updateStock(id, stock) {
        try {
            const query = `UPDATE products SET stock = ?, updated_at = NOW() WHERE id = ? AND status != 'deleted'`;
            const [result] = await this.pool.execute(query, [stock, id]);
            
            if (result.affectedRows === 0) {
                throw new Error('Product not found or already deleted');
            }
            
            return await this.findById(id);
        } catch (error) {
            throw new Error(`Database update failed: ${error.message}`);
        }
    }

    // 增加查看次数
    async incrementViewCount(id) {
        try {
            const query = `UPDATE products SET view_count = view_count + 1 WHERE id = ? AND status != 'deleted'`;
            await this.pool.execute(query, [id]);
        } catch (error) {
            throw new Error(`Database update failed: ${error.message}`);
        }
    }

    // 获取所有分类
    async getDistinctCategories() {
        try {
            const query = `
                SELECT DISTINCT category 
                FROM products 
                WHERE category IS NOT NULL AND category != '' AND status != 'deleted'
                ORDER BY category
            `;
            
            const [rows] = await this.pool.execute(query);
            return rows.map(row => row.category);
        } catch (error) {
            throw new Error(`Database query failed: ${error.message}`);
        }
    }

    // 获取价格范围
    async getPriceRange(filters = {}) {
        try {
            let query = `
                SELECT MIN(price) as min_price, MAX(price) as max_price 
                FROM products 
                WHERE status != 'deleted'
            `;
            const params = [];
            
            if (filters.category) {
                query += ` AND category = ?`;
                params.push(filters.category);
            }
            
            if (filters.search) {
                query += ` AND (name LIKE ? OR description LIKE ? OR sku LIKE ?)`;
                const searchTerm = `%${filters.search}%`;
                params.push(searchTerm, searchTerm, searchTerm);
            }
            
            const [rows] = await this.pool.execute(query, params);
            return rows[0];
        } catch (error) {
            throw new Error(`Database query failed: ${error.message}`);
        }
    }

    // 查找库存不足的产品
    async findLowStock(threshold = 10) {
        try {
            const query = `
                SELECT id, name, sku, stock, category
                FROM products 
                WHERE stock <= ? AND status = 'active'
                ORDER BY stock ASC
            `;
            
            const [rows] = await this.pool.execute(query, [threshold]);
            return rows;
        } catch (error) {
            throw new Error(`Database query failed: ${error.message}`);
        }
    }

    // 查找热门产品
    async findPopular(limit = 10) {
        try {
            const query = `
                SELECT id, name, price, sku, category, view_count
                FROM products 
                WHERE status = 'active'
                ORDER BY view_count DESC
                LIMIT ?
            `;
            
            const [rows] = await this.pool.execute(query, [limit]);
            return rows;
        } catch (error) {
            throw new Error(`Database query failed: ${error.message}`);
        }
    }

    // 获取产品统计信息
    async getStats() {
        try {
            const query = `
                SELECT 
                    COUNT(*) as total_products,
                    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_products,
                    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) as inactive_products,
                    SUM(CASE WHEN stock <= 10 THEN 1 ELSE 0 END) as low_stock_products,
                    AVG(price) as average_price,
                    SUM(stock) as total_stock
                FROM products
                WHERE status != 'deleted'
            `;
            
            const [rows] = await this.pool.execute(query);
            return rows[0];
        } catch (error) {
            throw new Error(`Database stats query failed: ${error.message}`);
        }
    }

    // 批量更新产品状态
    async batchUpdateStatus(productIds, status) {
        try {
            if (!Array.isArray(productIds) || productIds.length === 0) {
                throw new Error('Product IDs must be a non-empty array');
            }
            
            const placeholders = productIds.map(() => '?').join(',');
            const query = `UPDATE products SET status = ?, updated_at = NOW() WHERE id IN (${placeholders}) AND status != 'deleted'`;
            const params = [status, ...productIds];
            
            const [result] = await this.pool.execute(query, params);
            return result.affectedRows;
        } catch (error) {
            throw new Error(`Database batch update failed: ${error.message}`);
        }
    }

    // 批量更新库存
    async batchUpdateInventory(updates) {
        try {
            if (!Array.isArray(updates) || updates.length === 0) {
                throw new Error('Updates must be a non-empty array');
            }
            
            const connection = await this.getConnection();
            await this.beginTransaction(connection);
            
            try {
                const results = [];
                
                for (const update of updates) {
                    const { id, stock } = update;
                    const result = await super.update(this.tableName, id, { stock }, connection);
                    
                    results.push({
                        id,
                        success: result.affectedRows > 0,
                        affectedRows: result.affectedRows
                    });
                }
                
                await this.commitTransaction(connection);
                return results;
            } catch (error) {
                await this.rollbackTransaction(connection);
                throw error;
            } finally {
                connection.release();
            }
        } catch (error) {
            throw new Error(`Batch inventory update failed: ${error.message}`);
        }
    }

    // 获取低库存产品
    async getLowStockProducts(threshold = 10) {
        try {
            return await super.findAll(this.tableName, {
                select: 'id, name, sku, stock, category',
                where: 'stock <= ? AND status = ?',
                whereParams: [threshold, 'active'],
                orderBy: 'stock ASC'
            });
        } catch (error) {
            throw new Error(`Database query failed: ${error.message}`);
        }
    }

    // 获取产品分类列表
    async getCategories() {
        try {
            const rows = await super.findAll(this.tableName, {
                select: 'DISTINCT category',
                where: "category IS NOT NULL AND status != 'deleted'",
                orderBy: 'category ASC'
            });
            return rows.map(row => row.category);
        } catch (error) {
            throw new Error(`Database query failed: ${error.message}`);
        }
    }

    // 关闭数据库连接池
    async close() {
        try {
            await super.close();
        } catch (error) {
            throw new Error(`Failed to close database pool: ${error.message}`);
        }
    }
}